package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.*;
import com.hualude.roadsign.model.HighLeverEntity;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

/**
 * Created by xu_jiaHui on 2018/3/24.
 */
public interface HighLeverRepository extends CrudRepository<HighLeverEntity, Integer> {


    /**
     * @Description: 窨井产品DT展示
     * @author xu_jiaHui
     * @date 2017/8/8 15:15
     */
    public List<HighLeverDto> getHighLever4DT(DTRequestDto dtRequestDto,
                                              String actorIds,
                                              Integer orgId,
                                              Boolean audit,
                                              String startRoad,
                                              String endRoad,
                                              String roadName,
                                              Integer alarmBinding) throws Exception;

    /**
     * @Description: 窨井产品DT展示
     * @author xu_jiaHui
     * @date 2017/8/8 15:30
     */
    public Integer getHighLever4DTCount(DTRequestDto dtRequestDto,
                                        String actorIds,
                                        Integer orgId,
                                        Boolean audit,
                                        String startRoad,
                                        String endRoad,
                                        String roadName,
                                        Integer alarmBinding) throws Exception;


    /**
     * @Description: 通过ID删除窨井
     * @author xu_jiaHui
     * @date 2017/8/8 20:47
     */
    @Modifying
    @Query(value = "delete from HighLeverEntity  where highLeverId=?1")
    public Integer deleteHighLever(Integer highLeverId) throws Exception;

    /**
     * @Description: 更新窨井信息
     * @author xu_jiaHui
     * @date 2017/8/9 12:36
     */
    @Modifying
    @Query(value = "update HighLeverEntity set projectNumber=?2, highLeverLongitude=?3,highLeverLatitude=?4,  highLeverProvince=?5, " +
            " highLeverCity=?6, highLeverArea=?7,highLeverRoadName=?8,highLeverStartRoad=?9,highLeverEndRoad=?10,highLeverSize=?11," +
            "highLeverPhoto=?12,highLeverInstallTime=?13,highLeverInstallOrg=?14,highLeverPeriod=?15," +
            "highLeverServiceLife=?16,highLeverRemark=?17,highLeverCreatorId=?18,highLeverModifierId=?19,highLeverOrgId=?20,highLeverMainOrgId=?21," +
            "highLeverIsComplete=0 where highLeverId=?1")
    public Integer updateHighLever(Integer highLeverId,
                                   String projectNumber,
                                   String highLeverLongitude,
                                   String highLeverLatitude,
                                   String highLeverProvince,
                                   String highLeverCity,
                                   String highLeverArea,
                                   String highLeverRoadName,
                                   String highLeverStartRoad,
                                   String highLeverEndRoad,
                                   String highLeverSize,
                                   String highLeverPhoto,
                                   String highLeverInstallTime,
                                   String highLeverInstallOrg,
                                   Integer highLeverPeriod,
                                   Integer highLeverServiceLife,
                                   String highLeverRemark,
                                   Integer highLeverCreatorId,
                                   Integer highLeverModifierId,
                                   Integer highLeverOrgId,
                                   Integer highLeverMainOrgId) throws Exception;


    /**
     * @Description: 根据路名获取起始路段
     * @author xu_jiaHui
     * @date 2017/8/9 13:13
     */
    public List<String> getStartRoadByRoadName(String roadName, String actorIds, Integer orgId) throws Exception;

    /**
     * @Description: 根据所在路名 起始路名获取结束路名
     * @author xu_jiaHui
     * @date 2017/8/9 13:27
     */
    public List<String> getEndRoadByStartRoad(String city, String highLeverRoadName, String startRoad, String actorIds, Integer orgId) throws Exception;

    /**
     * @Description: 获取窨井信息用于列表展示4App
     * @author xu_jiaHui
     * @date 2017/8/9 14:09
     */
    public List<HighLeverDto> getHighLever4APP(String actorIds, Integer orgId, Integer mCount, Integer mSize, String city, String startRoad, String endRoad, String roadName, String alarmType) throws Exception;

    /**
     * @Description: 根据Id获取信号机信息
     * @Author: xu_jiaHui
     * @Date: 2017/8/10 16:38
     */
    public List<HighLeverDto> getHighLeverById(Integer highLeverId) throws Exception;

    /**
     * @Description: 获取信号机城市列表4App
     * @Author: xu_jiaHui
     * @Date: 2017/8/10 16:46
     */
    public List<String> getCity4HighLever4App(String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception;

    /**
     * @Description: 通过city获取路名列表
     * @Author: xu_jiaHui
     * @Date: 2017/8/10 16:52
     */
    public List<String> getRoadName4HighLeverByCity(String actorIds, Integer orgId, String city, Integer mCount, Integer mSize) throws Exception;

    /**
     * @Description: 根据 城市 路名 起始路名 获取结束路名
     * @Author: xu_jiaHui
     * @Date: 2017/8/11 19:59
     */
    public List<String> getEndRoadByStartRoad4App(String city, String highLeverRoadName, String startRoad, String actorIds, Integer orgId, Integer mCount, Integer mSize);

    /**
     * @Description: 根据 城市 路名 获取起始路名
     * @Author: xu_jiaHui
     * @Date: 2017/8/11 20:51
     */
    public List<String> getStartRoadByRoadName4App(String city, String roadName, String actorIds, Integer orgId, Integer mCount, Integer mSize) throws Exception;

    /**
     * @Description: 获取窨井信息用于app端地图展示
     * @Author: xu_jiaHui
     * @Date: 2017/8/11 22:36
     */
    public List<HighLeverDto> getHighLever4Map4App(Integer orgId,
                                                   String actorIds,
                                                   double neLongitude,
                                                   double neLatitude,
                                                   double swLongitude,
                                                   double swLatitude) throws Exception;

    /**
     * @Description: 信号机详情
     * @Author: pan-jianan
     * @Date: 2017/7/22 18:47
     */
    @Query(value = "select new com.hualude.roadsign.dto.HighLeverFaultDto(c.highLeverId,c.highLeverOrgId,c.highLeverMainOrgId," +
            "c.highLeverLongitude,c.highLeverLatitude,c.highLeverProvince,c.highLeverCity,c.highLeverArea,c.highLeverRoadName,c.highLeverStartRoad," +
            "c.highLeverEndRoad,c.highLeverSize,c.highLeverPhoto,c.highLeverInstallTime,c.highLeverInstallOrg," +
            "c.highLeverPeriod,c.highLeverServiceLife,c.highLeverCreatorId) " +
            "from HighLeverEntity c where c.highLeverId=?1")
    public HighLeverFaultDto getHighLeverInfo2Fault(Integer highLeverId) throws Exception;

    /**
     * @Description: 获取信号机坐标4MAP
     * @Author: pan-jianan
     * @Date: 2017/7/22 15:59
     */
    public List<HighLeverFaultDto> getHighLeverCoordinate4Map(Integer orgId, Integer actorId) throws Exception;

    /**
     * @Description: 根据坐标获取信号机信息
     * @Author: pan-jianan
     * @Date: 2017/7/22 22:11
     */
    @Query(value = "select new com.hualude.roadsign.dto.HighLeverFaultDto(c.highLeverId,c.highLeverOrgId,c.highLeverMainOrgId," +
            "c.highLeverLongitude,c.highLeverLatitude,c.highLeverProvince,c.highLeverCity,c.highLeverArea,c.highLeverRoadName,c.highLeverStartRoad," +
            "c.highLeverEndRoad,c.highLeverSize,c.highLeverPhoto,c.highLeverInstallTime,c.highLeverInstallOrg," +
            "c.highLeverPeriod,c.highLeverServiceLife,c.highLeverCreatorId) " +
            "from HighLeverEntity c where c.highLeverLongitude=?1 and c.highLeverLatitude=?2")
    public List<HighLeverFaultDto> getHighLeverByXY(String longitude, String latitude) throws Exception;

    /**
     * @Description: 信号机DT4故障上报
     * @Author: pan-jianan
     * @Date: 2017/7/22 23:26
     */
    public Integer getHighLeverFaultReportCount4DT(String search, Integer actorId, Integer orgId, String highLeverStartRoad, String highLeverRoadName, String highLeverEndRoad) throws Exception;

    public List<DTHighLeverFaultDto> getHighLeverFaultReport4DT(DTRequestDto dtRequestDto, Integer actorId, Integer orgId, String highLeverRoadName, String highLeverStartRoad, String highLeverEndRoad) throws Exception;

    /**
     * @Description: 获取第一个路名
     * @Author: pan-jianan
     * @Date: 2017/7/23 0:41
     */
    public List<String> getStartRoad(String roadName, Integer actorId, Integer orgId) throws Exception;

    /**
     * @Description: 根据第一个获取第二个路名
     * @Author: pan-jianan
     * @Date: 2017/7/23 0:41
     */
    public List<String> getSectionByStartRoad(String roadName, Integer actorId, Integer orgId) throws Exception;

    /**
     * @Description: 审核窨井信息
     * @Author: xu_jiaHui
     * @Date: 2017/8/12 18:32
     */
    @Modifying
    @Query(value = "update HighLeverEntity set highLeverIsComplete=1 where highLeverId=?1")
    public Integer auditHighLever(Integer highLeverId) throws Exception;

    /**
     * @Description: 获取窨井坐标用于地图显示
     * @Author: xu_jiaHui
     * @Date: 2017/8/13 19:53
     */
    public List<HighLeverDto> getHighLeverCoordinates4Map(Integer orgId, Integer orgType, Integer actorId) throws Exception;

    /**
     * @Description: 通过xy获取窨井信息
     * @Author: xu_jiaHui
     * @Date: 2017/8/13 21:27
     */
    @Query(value = "select new com.hualude.roadsign.dto.MapInfoDto(sm.highLeverPhoto,sm.highLeverId,sm.projectNumber," +
            "sm.highLeverProvince,sm.highLeverCity,sm.highLeverArea,sm.highLeverRoadName,sm.highLeverStartRoad,sm.highLeverEndRoad,sm.highLeverSize," +
            "sm.highLeverInstallTime,sm.highLeverInstallOrg,sm.highLeverPeriod,sm.highLeverServiceLife,sm.highLeverRemark," +
            "u.userName,o1.orgName,o2.orgName) from HighLeverEntity sm,OrgEntity o1,OrgEntity o2,UserEntity u where " +
            "o1.orgId=sm.highLeverOrgId and o2.orgId=sm.highLeverMainOrgId and sm.highLeverCreatorId=u.userId and sm.highLeverLongitude=?1 " +
            "and sm.highLeverLatitude=?2")
    public MapInfoDto getHighLeverInfoByXY(String longitude, String latitude) throws Exception;

    /**
     * @Description: 获取窨井数量
     * @Author: xu_jiaHui
     * @Date: 17-11-13 下午7:33
     */

    public Integer getHighLeverCount(String actorIds, Integer orgId) throws Exception;

    @Modifying
    @Query(value = "update HighLeverEntity set highLeverPatrolTime=?2,highLeverPatrolContent=?3,highLeverModifierId=?4 where highLeverId=?1")
    public Integer setUpPatrolRecord(Integer highLeverId, String highLeverPatrolTime, String highLeverPatrolContent, Integer highLeverModifierId);

    @Modifying
    @Query(value = "update HighLeverEntity set alarmDeviceId=?1 where highLeverId=?2 and alarmDeviceId=null")
    public Integer bindingDevice(String deviceId, Integer highLeverId) throws Exception;

    @Modifying
    @Query(value = "update HighLeverEntity set alarmDeviceId=null where highLeverId=?1")
    public Integer unbindingDevice(Integer highLeverId) throws Exception;

    public List<HighLeverDto> getDeviceInfo(Integer deviceId) throws Exception;


}
